/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.foxline.service;

import br.com.foxline.enums.TypeDocument;
import br.com.foxline.model.*;
import br.com.foxline.util.Caracter;
import br.com.foxline.util.Msg;
import java.util.List;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
@LocalBean
public class TitleDocumentService  extends GenericService<TitleDocument>{

    public TitleDocumentService() {
        super(TitleDocument.class);
    }
    
     @Override
    public void save(TitleDocument entity) {
        try{
            super.save(entity);
            Msg.messagemInfo("Cadastrado com Sucesso !");
        }catch(Exception e){
            Msg.messagemInfo("Erro !");
            e.printStackTrace();
        }
    }
     
     public List<TitleDocument> search(){
         
         
         return null;
     }
     
    public List<TitleDocument> searchTitleDocument(TitleDocument titleDocument, Part part, Stamp stamp) {

        Query query;
        String sql = "Select distinct(c) from TitleDocument c ";

        if (part != null) {
            boolean q = true;
            if (part.getPeople() != null) {
                if ((part.getPeople().getName() != null && !part.getPeople().getName().isEmpty())
                        || (part.getPeople().getTipoDocumento() == TypeDocument.CPF && part.getPeople().getCpf() != null && !part.getPeople().getCpf().isEmpty())
                        || (part.getPeople().getTipoDocumento() == TypeDocument.CNPJ && part.getPeople().getCnpj() != null && !part.getPeople().getCnpj().isEmpty())) {
                    sql += "  JOIN c.parts p  JOIN p.people peo ";
                    q = false;
                }
            }
            if (part.getPartQuality() != null) {
                if(q)
                    sql += "  JOIN c.parts p  ";
                sql += " JOIN p.partQuality pq  ";
            }
        }
        if (stamp != null && stamp.getTypeStamp() != null) {
                sql += " JOIN c.stamp st JOIN st.typeStamp ts";
            
        }

        sql += " where ";

        if (titleDocument.getProtocolo() != null && !titleDocument.getProtocolo().isEmpty()) {
            sql += " c.protocolo = :protocolo and  ";
        }
        
        if (titleDocument.getTypeTitleDocument() != null) {
            sql += " c.typeTitleDocument = :typeTitleDocument and  ";
        }
        if (titleDocument.getBook() != null && !titleDocument.getBook().isEmpty()) {
            sql += " c.book like :book  and ";
        }
        if (titleDocument.getBookComp() != null && !titleDocument.getBookComp().isEmpty()) {
            sql += " lower(c.bookComp) like lower(:bookComp)  and ";
        }
        if (titleDocument.getLeaf() != null && !titleDocument.getLeaf().isEmpty()) {
            sql += " c.leaf like :leaf and ";
        }
        if (titleDocument.getLeafComp() != null && !titleDocument.getLeafComp().isEmpty()) {
            sql += " lower(c.leafComp) like lower(:leafComp) and ";
        }
        if (titleDocument.getDatetdoc() != null) {
            sql += " c.datetdoc = :datetdoc and ";
        }
        
        if (titleDocument.getDatebp() != null) {
            sql += " c.datebp = :datebp and ";
        }
        if (titleDocument.getRegister() != null && !titleDocument.getRegister().isEmpty()) {
            sql += " lower(c.register) like lower(:register) and ";
        }

        if (part != null) {
            if (part.getPartQuality() != null) {
                sql += " pq.id = :partQuality and ";
            }
            People people = part.getPeople();
            if (people != null) {
                if (people.getName() != null && !people.getName().isEmpty()) {
                    sql += " lower(peo.name) like lower(:name) and ";
                }
                if (people.getTipoDocumento() == TypeDocument.CPF && people.getCpf() != null && !people.getCpf().isEmpty()) {
                    sql += " lower(peo.cpf) like lower(:doc)  and ";
                } else if (people.getTipoDocumento() == TypeDocument.CNPJ && people.getCnpj() != null && !people.getCnpj().isEmpty()) {
                    sql += " lower(peo.cnpj) like lower(:doc)  and ";
                }

            }
        }
        if (stamp != null) {
            if (stamp.getSerie() != null && !stamp.getSerie().isEmpty()) {
                sql += " st.serie like :serie and ";
            }
            if (stamp.getNumber() != null) {
                sql += " st.number = :number and ";
            }
            if (stamp.getTypeStamp() != null) {
                sql += " ts.id = :idTypeStamp  and ";
            }
        }

        sql += " 1=1 ORDER BY c.register ASC";

        query = getEntityManager().createQuery(sql);
        if (titleDocument.getBook() != null && !titleDocument.getBook().isEmpty()) {
            query.setParameter("book", titleDocument.getBook());
        }
        if (titleDocument.getBookComp() != null && !titleDocument.getBookComp().isEmpty()) {
            query.setParameter("bookComp", titleDocument.getBookComp());
        }
        if (titleDocument.getLeaf() != null && !titleDocument.getLeaf().isEmpty()) {
            query.setParameter("leaf", titleDocument.getLeaf());
        }
        if (titleDocument.getLeafComp() != null && !titleDocument.getLeafComp().isEmpty()) {
            query.setParameter("leafComp", titleDocument.getLeaf());
        }
        if (titleDocument.getDatetdoc() != null) {
            query.setParameter("datetdoc", titleDocument.getDatetdoc());
        }
        
         if (titleDocument.getDatebp() != null) {
            query.setParameter("datebp", titleDocument.getDatebp());
        }
        if (titleDocument.getRegister() != null && !titleDocument.getRegister().isEmpty()) {
            query.setParameter("register", titleDocument.getRegister());
        }
        
        if (titleDocument.getProtocolo() != null&& !titleDocument.getProtocolo().isEmpty()) {
            query.setParameter("protocolo", titleDocument.getProtocolo());
        }
        if (titleDocument.getTypeTitleDocument() != null) {
            query.setParameter("typeTitleDocument", titleDocument.getTypeTitleDocument());
        }
        
        if (part != null) {
            if (part.getPartQuality() != null) {
                query.setParameter("partQuality", part.getPartQuality().getId());
            }
            if (part.getPeople() != null) {
                if (part.getPeople().getName() != null && !part.getPeople().getName().isEmpty()) {
                    query.setParameter("name", part.getPeople().getName() + "%");
                }
                if ((part.getPeople().getCpf() != null && !part.getPeople().getCpf().isEmpty())
                        || (part.getPeople().getCnpj() != null && !part.getPeople().getCnpj().isEmpty())) {
                    query.setParameter("doc",
                            part.getPeople().getTipoDocumento() == TypeDocument.CPF ? Caracter.removecaracter(part.getPeople().getCpf()) : Caracter.removecaracter(part.getPeople().getCnpj()));
                }
            }
        }
        if (stamp != null) {
            if (stamp.getSerie() != null && !stamp.getSerie().isEmpty()) {
                query.setParameter("serie", stamp.getSerie());
            }
            if (stamp.getNumber() != null) {
                query.setParameter("number", stamp.getNumber());
            }
            if (stamp.getTypeStamp() != null) {
                query.setParameter("idTypeStamp", stamp.getTypeStamp().getId());
            }
        }
        System.err.println(sql);
        return query.getResultList();
    }
}
